SQLite常用函数及语句
SQLite3.0使用的是C的函数接口，常用函数如下：
sqlite3_open() //打开数据库
sqlite3_close() //关闭数据库
sqlite3_exec() //执行sql语句,例如创建表
sqlite3_prepare_v2() //编译SQL语句
sqlite3_step() //执行查询SQL语句
sqlite3_finalize() //结束sql语句
sqlite3_bind_text() //绑定参数
sqlite3_column_text() //查询字段上的数据
创建数据库表
sqlite3 *sqlite = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",@"data.sqlite"];
int result = sqlite3_open([filePath UTF8String],&sqlite);
if (result != SQLITE_OK) {
NSLog(@"打开数据失败");
}
//创建表的SQL语句
NSString *sql = @"CREATE TABLE UserTable (userId text NOT NULL PRIMARY KEY UNIQUE,username text, age integer)";
char *error;
//执行SQL语句
result = sqlite3_exec(sqlite,[sql UTF8String],NULL, NULL, &error);
if (result != SQLITE_OK) {
NSLog(@"创建数据库失败，%s",erorr);
}
sqlite_close(sqlite);
插入数据
sqlite3 *sqlite = nil;
sqlite3_stmt = *stmt = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",@"data.sqlite"];
int result = sqlite3_open([filePath UTF8String],&sqlite);
if (result = SQLITE_OK) {
NSLog(@"打开数据失败");
}
NSString *sql = @"INSERT INTO UserTable(userId,userName,age) VALUES(?,?,?)";
sqlite3_prepare_v2(sqlite, [sql UTF8String], -1, &stmt ,NULL);
NSString *userId = @"1002";
NSString *username = @"张三";
int age = 3;
//往SQL中填充数据
sqlite3_bind_text(stmt, 1, [userId UTF8String], -1, NULL);
sqlite3_bind_text(stmt, 2, [userName UTF8String], -1,NULL);
sqlite3_bind_int(stmt, 3, age);
result = sqlite3_step(stmt);
if (result == SQLITE_ERROR || result == SQLITE_MISUSE) {
NSLog(@"执行SQL语句失败");
return NO;
}
sqlite3_finalize(stmt);
sqlite3_close(sqlite);
查询数据
sqlite3 *sqlite = nil;
sqlite3_stmt *stmt = nil;
NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",@"data.sqlite"];
int result = sqlite3_open([filePath UTF8String],&sqlite);
if (result != SQLITE_OK) {
NSLog(@"打开数据失败");
}
NSString *sql = @"SELECT userId, userName,age FROM UserTable WHERE age >?";
sqlite3_prepare_v2(sqlite, [sql UTF8String], -1,&stmt, NULL);
int age = 1;
sqlite3_bind_int(stmt, 1,age);
result = sqlite3_step(stmt);
//循环遍历查询后的数据列表
while (result == SQLITE_ROW) {
char *userid = (char*)sqlite3_column_text(stmt,0);
char *username = (char*)sqlite3_column_text(stmt,1);
int age = sqlite3_column_int(stmt,2);
NSString *userId = [NSString stringWithCString:userid encoding:NSUTF8StringEncoding];
NSString *userName = [NSString stringWithCString:username encoding:NSUTF8StringEncoding];
NSLog(@"-----用户名：%@,用户id:%@,年龄：%d---",userName,userId,age);
result = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(sqlite);
